package com.tansun.tandata.utils;

import java.io.BufferedInputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.google.common.base.Preconditions;

import lombok.extern.slf4j.Slf4j;

/**
 * @Description excel工具类
 * @Author will
 * @Date 2018/12/12 0012 上午 10:29
 */
@Slf4j
public class ExcelUtils {
    public static final String EXCEL_07_SUFFIX = ".xlsx";//excel2007后缀

    private static final ThreadLocal<Map<String, CellStyle>> styleCache = new ThreadLocal();

    private static final String NUMERICSTYLE ="NUMERICSTYLE";

    private static final String TEXTSTYLE ="TEXTSTYLE";


    /**
     * description: 生成单sheet页的excel文件
     *
     * @param: [fullPathName, layoutInfo, dataList]
     * @return: void
     * @auther: will
     * @date: 2018/12/13 0013 上午 9:32
     */
    public static void createSingleSheetExcel(String fullPathName, SheetLayoutInfo layoutInfo, List<Map<String, Object>> dataList) {
        XSSFWorkbook wb = null;
        OutputStream out = null;
        try {
            out = new FileOutputStream(new File(fullPathName));
            wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet(layoutInfo.getSheetName());
            Map<String, Object> headMap = new HashMap<>();
            if (dataList.size() > 0) {
                headMap = dataList.get(0);
            }
            XSSFRow headRow = sheet.createRow(0);
            String[] customHeadNames = layoutInfo.getHeadNames();
            //设值excel头信息
            int headColumnIndex = 0;
            if (customHeadNames == null || customHeadNames.length == 0) {
                //默认设置sql查询的字段名
                for (String key : headMap.keySet()) {
                    XSSFCell cell = headRow.createCell(headColumnIndex);
                    ExcelUtils.setCellStyleAndValue(wb, cell, layoutInfo, key);
                    headColumnIndex++;
                }
            } else {
                //设自定义列名称
                for (String name : customHeadNames) {
                    XSSFCell cell = headRow.createCell(headColumnIndex);
                    ExcelUtils.setCellStyleAndValue(wb, cell, layoutInfo,name);
                    headColumnIndex++;
                }
            }
            //填充excel数据项
            for (int i = 0; i < dataList.size(); i++) {
                XSSFRow dataRow = sheet.createRow(i + 1);
                int j = 0;
                for (Map.Entry<String, Object> entry : dataList.get(i).entrySet()) {
                    XSSFCell cell = dataRow.createCell(j);
                    ExcelUtils.setCellStyleAndValue(wb, cell, layoutInfo, entry.getValue());
                    j++;
                }
            }
            wb.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(out);
        }
    }

    /**
     * description: 生成多个sheet页的excel文件
     *
     * @param: [fullPathName, infoList, dataList]
     * @return: void
     * @auther: will
     * @date: 2018/12/13 0013 上午 9:35
     */
    public static void createSheetsExcel(String fullPathName, List<SheetLayoutInfo> infoList, List<List<Map<String, Object>>> dataList) {
        XSSFWorkbook wb = null;
        OutputStream out = null;
        try {
            if (infoList.size() != dataList.size()) {
                throw new RuntimeException("excel creating param error");
            }
            out = new FileOutputStream(new File(fullPathName));
            wb = new XSSFWorkbook();
            fillExcelDatas(infoList, dataList, wb);
            wb.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(out);
            IOUtils.closeQuietly(wb);
        }
    }

    /**
     * description: 生成workbook对象并转换成流
     *
     * @param: [infoList, dataList]
     * @return: java.io.InputStream
     * @auther: will
     * @date: 2018/12/13 0013 上午 10:42
     */
    public static InputStream createWorkbookAnd2Stream(List<SheetLayoutInfo> infoList, List<List<Map<String, Object>>> dataList) {
    	XSSFWorkbook wb = null;
        ByteArrayInputStream bis = null;
        ByteArrayOutputStream bos = null;
        try {
            if (infoList.size() != dataList.size()) {
                throw new RuntimeException("excel exporting param error");
            }
            wb = new XSSFWorkbook();
            //填充excel数据
            List list = fillExcelDatas(infoList, dataList, wb);
            bos = new ByteArrayOutputStream();
            wb.write(bos);
            bis = new ByteArrayInputStream(bos.toByteArray());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(wb);
            IOUtils.closeQuietly(bos);
            IOUtils.closeQuietly(bis);
        }
        return bis;
    }

    private static List fillExcelDatas(List<SheetLayoutInfo> infoList, List<List<Map<String, Object>>> dataList, XSSFWorkbook wb) {
        List sheetList = new ArrayList();
        int index = 0;//计数器
        for (List<Map<String, Object>> sheetDataList : dataList) {
            SheetLayoutInfo layoutInfo = infoList.get(index);
            index++;
            XSSFSheet sheet = wb.createSheet(layoutInfo.getSheetName());
            sheetList.add(sheet);
            Map<String, Object> headMap = new HashMap<>();
            if (sheetDataList.size() > 0) {
                headMap = sheetDataList.get(0);
            }
            XSSFRow headRow = sheet.createRow(0);
            String[] customHeadNames = layoutInfo.getHeadNames();
            //设值excel头信息
            int headColumnIndex = 0;
            if (customHeadNames == null || customHeadNames.length == 0) {
                //默认设置sql查询的字段名
                for (String key : headMap.keySet()) {
                    XSSFCell cell = headRow.createCell(headColumnIndex);
                    ExcelUtils.setCellStyleAndValue(wb, cell, layoutInfo, key);
                    headColumnIndex++;
                }

            } else {
                //设自定义列名称
                for (String name : customHeadNames) {
                    XSSFCell cell = headRow.createCell(headColumnIndex);
                    ExcelUtils.setCellStyleAndValue(wb, cell, layoutInfo, name);
                    headColumnIndex++;
                }
            }
            //填充excel数据项
            for (int i = 0; i < sheetDataList.size(); i++) {
                XSSFRow dataRow = sheet.createRow(i + 1);
                int j = 0;
                for (Map.Entry<String, Object> entry : sheetDataList.get(i).entrySet()) {
                    XSSFCell cell = dataRow.createCell(j);
                    //ExcelUtils.setCellStyleAndValue(wb, cell, layoutInfo, entry.getValue());
                    if(entry.getValue()==null ) {
                   	  	cell.setCellValue("");
                    }else {
                    	cell.setCellValue(String.valueOf(entry.getValue()));
                    }
                    j++;
                }
            }
            //处理自定义合并统计
            if (sheetDataList.size() > 0 && layoutInfo.isHasSum()) {
                CustomSumInfo info = layoutInfo.getCustomSumInfo();
                create2MergeCells(wb, sheet, info.getRowNum(), info.getMergeSize(), info.getColumnSize(), info.getSumInitRow());
            }

        }
        return sheetList;
    }

    /**
     * easyExcel读取excel
     *
     * @param in
     * @param clazz
     * @return
     */
    public static List<Object> readExcelWithModel(InputStream in, Class<? extends BaseRowModel> clazz) {
        List<Object> resultList = null;
        try {
            resultList = EasyExcelFactory.read(in, new Sheet(1, 1, clazz));

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(in);
        }
        return resultList;
    }

    public static <T extends BaseRowModel> List<T> readByReturnModel(InputStream in, Class<T> clazz) {
        List<T> rows = new ArrayList<>();
        AnalysisEventListener myListener = new AnalysisEventListener<T>() {
            @Override
            public void invoke(T t, AnalysisContext analysisContext) {
                rows.add(t);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            	log.info("data analysis finish");
            }
        };
        ExcelReader reader = EasyExcelFactory.getReader(in, myListener);
        Sheet sheet = new Sheet(1, 1,clazz);
        reader.read(sheet);
        return rows;
    }

    /**
     * 合并单元格，并设置合计公式
     *
     * @param sheet
     * @param rowNum     要操作的行号
     * @param columnSize 列数量
     * @param mergeSize  合并的单元格数目
     * @param sumInitRow 开始求和统计的行号
     */
    public static void create2MergeCells(Workbook wb, org.apache.poi.ss.usermodel.Sheet sheet, int rowNum, int mergeSize, int columnSize, int sumInitRow) {
        Preconditions.checkState(mergeSize <= columnSize, "cells amount cannot more than cloumns amount");
        Row row = sheet.createRow(rowNum);
        Cell cell = row.createCell(0);
        cell.setCellValue("合计：");
        //设置合并的单元格水平居右
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.RIGHT);
        cell.setCellStyle(style);
        for (int i = 1; i < mergeSize; i++) {
            row.createCell(i);
        }
        //合并单元格
        CellRangeAddress region = new CellRangeAddress(rowNum, rowNum, 0, mergeSize - 1);
        sheet.addMergedRegion(region);
        for (int j = mergeSize; j < columnSize; j++) {
            Cell sumCell = row.createCell(j);
            String colString = CellReference.convertNumToColString(j);
            String formulaVal;
            if (rowNum == 2) {
                formulaVal = "SUM(" + colString + sumInitRow + ")";
            }else {
                formulaVal  = "SUM(" + colString + sumInitRow + ":" + colString + rowNum + ")";

            }
            sumCell.setCellFormula(formulaVal);
        }

    }

    /**
     * description:给单元格设值和样式
     *
     * @param: [wb, cell, value]
     * @return: void
     * @auther: will
     * @date: 2018/12/12 0012 上午 11:13
     */
    public static void setCellStyleAndValue(Workbook wb, Cell cell,SheetLayoutInfo layoutInfo, Object value) {
        CellStyle style;
        DataFormat format = wb.createDataFormat();
        if (value instanceof Number) {
            BigDecimal bigDecimal = new BigDecimal(String.valueOf(value));
            StringBuilder formatStr = new StringBuilder("0");
            for (int i = 0; i < bigDecimal.scale(); i++) {
                if (i == 0) {
                    formatStr.append(".");
                }
                formatStr.append("0");
            }

            if (!Objects.isNull(styleCache.get()) && styleCache.get().containsKey(NUMERICSTYLE)) {
                style = styleCache.get().get(NUMERICSTYLE);
            } else {
                style = wb.createCellStyle();

                if (!Objects.isNull(layoutInfo.getStyleCallBack())) {
                    style = layoutInfo.getStyleCallBack().applyCellStyle(wb);
                }
                style.setDataFormat(format.getFormat(formatStr.toString()));
                if (!Objects.isNull(styleCache.get())) {
                    styleCache.get().put(NUMERICSTYLE, style);
                } else {
                    Map<String, CellStyle> map = new HashMap<>();
                    map.put(NUMERICSTYLE, style);
                    styleCache.set(map);
                }

            }
            cell.setCellStyle(style);
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(bigDecimal.doubleValue());
        } else {
            if (!Objects.isNull(styleCache.get()) && styleCache.get().containsKey(TEXTSTYLE)) {
                style = styleCache.get().get(TEXTSTYLE);
            } else {
                style = wb.createCellStyle();

                if (!Objects.isNull(layoutInfo.getStyleCallBack())) {
                    style = layoutInfo.getStyleCallBack().applyCellStyle(wb);
                }
                style.setDataFormat(format.getFormat("@"));//文本格式
                if (!Objects.isNull(styleCache.get())) {
                    styleCache.get().put(TEXTSTYLE, style);
                } else {
                    Map<String, CellStyle> map = new HashMap<>();
                    map.put(TEXTSTYLE, style);
                    styleCache.set(map);
                }

            }
            cell.setCellType(CellType.STRING);
            cell.setCellStyle(style);
            if(value==null ) {
           	  	cell.setCellValue("");
            }else {
            	cell.setCellValue(String.valueOf(value));
            }
        }

    }
    
    /*************************************** 根据模板导出excel **********************************/
    
    /**
     * description: 模板导出并转换成流
     * @param fileName 
     *
     * @param: [infoList, dataList]
     * @return: java.io.InputStream
     * @auther: will
     * @date: 2018/12/13 0013 上午 10:42
     */
    public static InputStream createTempAndStream(String tempType, String tempFilePath, List<SheetLayoutInfo> infoList, List<List<Map<String, Object>>> dataList) {
    	InputStream is = null;
    	XSSFWorkbook wb = null;
        ByteArrayInputStream bis = null;
        ByteArrayOutputStream bos = null;
        try {
            if (infoList.size() != dataList.size()) {
                throw new RuntimeException("excel exporting param error");
            }
            //is = new FileInputStream(tempFilePath);// 将excel文件转为输入流
            ClassPathResource cpr = new ClassPathResource("/templates/"+tempFilePath);
            is = cpr.getInputStream();
            wb = new XSSFWorkbook(is);
            //填充excel数据
            if(Constants.TEM_TYPE_ONE.equals(tempType)) {
            	List list = fillTempExcelDatas(infoList, dataList, wb);
            }else {
            	List list = fillTempHeadExcelDatas(infoList, dataList, wb);
            }
            
            bos = new ByteArrayOutputStream();
            wb.write(bos);
            bis = new ByteArrayInputStream(bos.toByteArray());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(wb);
            IOUtils.closeQuietly(bos);
            IOUtils.closeQuietly(bis);
        }
        return bis;
    }
    
    /**
     * 模板数据填充（已画好模板）
     * @param infoList
     * @param dataList
     * @param wb
     * @return
     */
    private static List fillTempExcelDatas(List<SheetLayoutInfo> infoList, List<List<Map<String, Object>>> dataList, XSSFWorkbook wb) {
        List sheetList = new ArrayList();
        int index = 0;//计数器
        for (List<Map<String, Object>> sheetDataList : dataList) {
            SheetLayoutInfo layoutInfo = infoList.get(index);
            //获取模板sheet页
            XSSFSheet sheet = wb.getSheetAt(layoutInfo.getSheetStart()+index);
            index++;
            sheetList.add(sheet);
            //填充excel数据项
            for (int i = 0; i < sheetDataList.size(); i++) {
                XSSFRow dataRow = sheet.getRow(i + layoutInfo.getDataStartRow());
                int j = 0;
                for (Map.Entry<String, Object> entry : sheetDataList.get(i).entrySet()) {
                    XSSFCell cell = dataRow.getCell(j+layoutInfo.getDataStartColumn());
                    if(entry.getValue()==null ) {
                    	 cell.setCellValue("");
                    }else {
                    	cell.setCellValue(String.valueOf(entry.getValue()));
                    }
                    j++;
                }
            }
            //指定单元格位置值填充
            List<CustomCell> cellList = layoutInfo.getCellValue();
            if (layoutInfo.isHasCellValue() && cellList.size() >0) {
            	for (CustomCell cell:cellList) {
                    XSSFRow dataRow = sheet.getRow(cell.getRowNum());
                    XSSFCell cellCol = dataRow.getCell(cell.getColNum());
                    cellCol.setCellValue(cell.getValue());                   
                }
            }
            //处理自定义合并统计
            if (sheetDataList.size() > 0 && layoutInfo.isHasSum()) {
                CustomSumInfo info = layoutInfo.getCustomSumInfo();
                create2MergeCells(wb, sheet, info.getRowNum(), info.getMergeSize(), info.getColumnSize(), info.getSumInitRow());
            }
        }
        return sheetList;
    }
    
    
    /**
     * 模板数据填充（只有表头和列名）
     * 区别在于是否创建单元格
     * @param infoList
     * @param dataList
     * @param wb
     * @return
     */
    private static List fillTempHeadExcelDatas(List<SheetLayoutInfo> infoList, List<List<Map<String, Object>>> dataList, XSSFWorkbook wb) {
        List sheetList = new ArrayList();
        int index = 0;//计数器
        for (List<Map<String, Object>> sheetDataList : dataList) {
            SheetLayoutInfo layoutInfo = infoList.get(index);
            //获取模板sheet页
            XSSFSheet sheet = wb.getSheetAt(layoutInfo.getSheetStart()+index);
            index++;
            sheetList.add(sheet);
            //填充excel数据项
            for (int i = 0; i < sheetDataList.size(); i++) {
                XSSFRow dataRow = sheet.createRow(i + layoutInfo.getDataStartRow());
                int j = 0;
                for (Map.Entry<String, Object> entry : sheetDataList.get(i).entrySet()) {
                	XSSFCell cell = dataRow.createCell(j+layoutInfo.getDataStartColumn());
                    //ExcelUtils.setCellStyleAndValue(wb, cell, layoutInfo, entry.getValue());
                	if(entry.getValue()==null ) {
                		cell.setCellValue("");
                    }else {
                    	cell.setCellValue(String.valueOf(entry.getValue()));
                    }
                    j++;
                }
            }
            //指定单元格位置值填充
            List<CustomCell> cellList = layoutInfo.getCellValue();
            if (layoutInfo.isHasCellValue() && cellList.size() >0) {
            	for (CustomCell cell:cellList) {
                    XSSFRow dataRow = sheet.getRow(cell.getRowNum());
                    XSSFCell cellCol = dataRow.getCell(cell.getColNum());
                    cellCol.setCellValue(cell.getValue());                   
                }
            }
            //处理自定义合并统计
            if (sheetDataList.size() > 0 && layoutInfo.isHasSum()) {
                CustomSumInfo info = layoutInfo.getCustomSumInfo();
                create2MergeCells(wb, sheet, info.getRowNum(), info.getMergeSize(), info.getColumnSize(), info.getSumInitRow());
            }
        }
        return sheetList;
    }
    
    
    /**
     * 下载文件
     * @param in
     * @param fileName
     * @param response
     */
	public static void downloadFile(InputStream in, String fileName, HttpServletResponse response) {
		
		BufferedInputStream bufInStream = null;
		OutputStream outStream = null;
		try {
			
			bufInStream = new BufferedInputStream(in);
//			String nameStr = new String(fileName.getBytes("GBK"), "ISO8859-1");
//			response.reset();
//			response.setContentType("application/x-msdownload");
//			response.setHeader("Content-Disposition", "attachment; filename=\"" + nameStr+"\"");
			
			response.setContentType("application/octet-stream");
            response.setHeader("Cache-control", "no-cache, no-store, must-revalidate");
            response.setHeader("Pragma", "no-cache");
	        response.setCharacterEncoding("UTF-8");
            //设置响应头部，以及文件名进行中文防止乱码转码操作
	        response.setHeader("Content-Disposition","attachment;filename="+URLEncoder.encode(fileName, "UTF-8"));
	        
		    byte[] buffer = new byte[10240];
			int len = 0;
			outStream = response.getOutputStream();
			while ((len = bufInStream.read(buffer)) > 0) {
				outStream.write(buffer, 0, len);
			}
			outStream.flush();
		}
		catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		catch (IOException e) {
			e.printStackTrace();
		}
		finally {
			try {
				if (in != null) {
					in.close();
				}
				if (bufInStream != null) {
					bufInStream.close();
				}
				if (outStream != null) {
					outStream.close();
				}
			}
			catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
}
